﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SiGeSIB.Data.Providers;
using SiGeSIB.Data.Finances;
using System.Data.SqlClient;
using System.Data;
using SiGeSIB.Data.Common;
using SiGeSIB.Data.Management;

namespace SiGeSIB.Data.Specialized.Providers
{
	public class FinancesDataProvider : BaseDataProvider, IFinancesDataProvider
	{
		private Movimentation _ReadMovimentation(SqlDataReader dr)
		{
			int IDX_Id = dr.GetOrdinal("Id");
			int IDX_IdCentroCusto = dr.GetOrdinal("IdCentroCusto");
			int IDX_IdContribuinte = dr.GetOrdinal("IdContribuinte");
			int IDX_IdTipoMovimentacao = dr.GetOrdinal("IdTipoMovimentacao");
			int IDX_DataMovimentacao = dr.GetOrdinal("DataMovimentacao");
			int IDX_Observacoes = dr.GetOrdinal("Observacoes");
			int IDX_Valor = dr.GetOrdinal("Valor");
			int IDX_IdTipoEntrada = dr.GetOrdinal("IdTipoEntrada");
			int IDX_IdTipoPagamento = dr.GetOrdinal("IdTipoPagamento");
			int IDX_NumeroCheque = dr.GetOrdinal("NumeroCheque");

			Movimentation m = MovimentationFactory.Create(dr.GetEnum<EMovimentationType>(IDX_IdTipoMovimentacao));
			m.Id = dr.GetInt32(IDX_Id);
			m.CostCenterId = dr.GetInt32(IDX_IdCentroCusto);
			m.ContributorId = dr.GetInt32(IDX_IdContribuinte);
			m.MovimentationDate = dr.GetDateTime(IDX_DataMovimentacao);
			m.Observation = !dr.IsDBNull(IDX_Observacoes) ? dr.GetString(IDX_Observacoes) : null;
			m.Amount = dr.GetDecimal(IDX_Valor);
			m.PaymentMethod = new PaymentMethod();
			m.PaymentMethod.Method = dr.GetEnum<EPaymentMethod>(IDX_IdTipoPagamento);
			m.PaymentMethod.CheckNumber = !dr.IsDBNull(IDX_NumeroCheque) ? dr.GetString(IDX_NumeroCheque) : null;

			if (m.Type == EMovimentationType.Income)
				(m as Income).IncomeType = dr.GetEnum<EIncomeType>(IDX_IdTipoEntrada);

			return m;
		}
		private CostCenter _ReadCostCenter(SqlDataReader dr)
		{
			int IDX_Id = dr.GetOrdinal("Id");
			int IDX_Nome = dr.GetOrdinal("NomeCentroCusto");
			int IDX_Descricao = dr.GetOrdinal("Descricao");
			int IDX_IdResponsavel = dr.GetOrdinal("IdResponsavel");
			int IDX_Codigo = dr.GetOrdinal("Codigo");

			CostCenter c = new CostCenter();

			c.Id = dr.GetInt32(IDX_Id);
			c.Name = dr.GetString(IDX_Nome);
			c.Description = !dr.IsDBNull(IDX_Descricao) ? dr.GetString(IDX_Descricao) : null;
			c.ResponsableId = !dr.IsDBNull(IDX_IdResponsavel) ? (int?)dr.GetInt32(IDX_IdResponsavel) : null;
			c.Code = dr.GetString(IDX_Codigo);

			return c;
		}
		
		internal void _ReadMovimentations(SqlDataReader dr, Action<Movimentation> action)
		{
			while (dr.Read())
			{
				Movimentation m = _ReadMovimentation(dr);
				action(m);
			}
		}
		internal void _ReadCostCenters(SqlDataReader dr, Action<CostCenter> action)
		{
			while (dr.Read())
				action(_ReadCostCenter(dr));
		}
		internal List<Movimentation> _GetMovimentations(SqlConnection conn, MovimentationFilter filter, MovimentationInfo info)
		{
			List<Movimentation> movimentations = null;

			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "GetMovimentacoes";
				cmd.AddParameterWithValues("Info", (int)info);
				cmd.AddParameterWithValues("Sep", SqlExtensions.CARRIAGE_RETURN);

				if(filter != null)
				{
					cmd.AddParameterWithValues("Ids", SqlExtensions.GetStringIds(filter.DesiredIds));
					cmd.AddParameterWithValues("IdsCentrosCusto", SqlExtensions.GetStringIds(filter.DesiredCostCentersIds));

					if (filter.DesiredInterval != null)
					{
						if (filter.DesiredInterval.Start.HasValue)
							cmd.AddParameterWithValues("InicioIntervalo", filter.DesiredInterval.Start.Value);

						if (filter.DesiredInterval.End.HasValue)
							cmd.AddParameterWithValues("FimIntervalo", filter.DesiredInterval.End.Value);
					}

					if (filter.DesiredType.HasValue)
						cmd.AddParameterWithValues("IdTipo", (int)filter.DesiredType.Value);
				}

				using (SqlDataReader dr = cmd.ExecuteReader())
				{
					Dictionary<int, CostCenter> costCenters = null;
					Dictionary<int, Contributor> contributors = null;
					bool loadSpecific = (info & MovimentationInfo.SpecificData) != 0;

					if (loadSpecific)
					{
						if (dr.HasRows)
						{
							costCenters = new Dictionary<int, CostCenter>();

							_ReadCostCenters(
								dr,
								cc =>
								{
									if (!costCenters.ContainsKey(cc.Id))
										costCenters.Add(cc.Id, cc);
								});
						}
						if (dr.NextResult())
						{
							contributors = new Dictionary<int, Contributor>();

							ManagementDataProvider manProv = new ManagementDataProvider();
							manProv._ReadContributors(
								dr,
								c =>
								{
									if (!contributors.ContainsKey(c.Id))
										contributors.Add(c.Id, c);
								},
								false);
						}
					}
					if (!loadSpecific || dr.NextResult())
					{
						_ReadMovimentations(
							dr,
							mov =>
							{
								if (movimentations == null)
									movimentations = new List<Movimentation>();

								if (loadSpecific)
								{
									if (!costCenters.IsNullOrEmpty())
									{
										costCenters[mov.CostCenterId].Movimentations.Add(mov);
										mov.CostCenter = costCenters[mov.CostCenterId];
									}

									if (!contributors.IsNullOrEmpty())
										mov.Contributor = contributors[mov.ContributorId];
								}

								movimentations.Add(mov);
							});
					}
				}
			}

			return movimentations;
		}
		internal List<CostCenter> _GetCostCenters(SqlConnection conn, int[] ids, CostCenterInfo info)
		{
			List<CostCenter> centers = null;
			
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "GetCentrosCusto";

				cmd.AddParameterWithValues("Ids", SqlExtensions.GetStringIds(ids));
				cmd.AddParameterWithValues("Sep", SqlExtensions.CARRIAGE_RETURN);
				cmd.AddParameterWithValues("Info", (int)info);

				using (SqlDataReader dr = cmd.ExecuteReader())
				{
					bool loadSpecific = (info & CostCenterInfo.SpecificData) != 0;
					Dictionary<int, Contributor> contributors = null;

					if (loadSpecific)
					{
						if (dr.HasRows)
						{
							contributors = new Dictionary<int, Contributor>();

							ManagementDataProvider manProv = new ManagementDataProvider();
							manProv._ReadContributors(
								dr,
								c =>
								{
									contributors.Add(c.Id, c);
								},
								false);
						}
					}
					if (!loadSpecific || dr.NextResult())
						_ReadCostCenters
							(
								dr,
								center =>
								{
									if (centers == null)
										centers = new List<CostCenter>();

									if (!contributors.IsNullOrEmpty() && center.ResponsableId.HasValue)
										center.Responsable = contributors[center.ResponsableId.Value] as Member;

									centers.Add(center);
								});
				}
			}

			return centers;
		}

		#region IFinancesDataProvider Members

		public int InsertMovimentation(Movimentation movimentation)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.AddParameterWithValues("Valor", movimentation.Amount);

				cmd.AddParameterWithValues("IdCentroCusto", movimentation.CostCenterId);

				cmd.AddParameterWithValues("DataMovimentacao", movimentation.MovimentationDate.Truncate());

				if (movimentation.Observation != null)
					cmd.AddParameterWithValues("Observacoes", movimentation.Observation);

				cmd.AddParameterWithValues("IdContribuinte", movimentation.ContributorId);

				if (movimentation.PaymentMethod != null)
				{
					cmd.AddParameterWithValues("IdTipoPagamento", (int)movimentation.PaymentMethod.Method);

					if (!string.IsNullOrEmpty(movimentation.PaymentMethod.CheckNumber))
						cmd.AddParameterWithValues("NumeroCheque", movimentation.PaymentMethod.Method);
				}

				switch (movimentation.Type)
				{
					case EMovimentationType.Income:
						cmd.CommandText = "InserirMovimentacaoEntrada";
						cmd.AddParameterWithValues("IdTipoEntrada", (int)(movimentation as Income).IncomeType);
						break;
					case EMovimentationType.Outcome:
						cmd.CommandText = "InserirMovimentacaoSaida";
						break;
					default:
						break;
				}

				return cmd.ExecuteWithReturnValue();
			}
		}
		public void UpdateMovimentation(Movimentation movimentation)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.AddParameterWithValues("Id", movimentation.Id);
				cmd.AddParameterWithValues("Valor", movimentation.Amount);

				cmd.AddParameterWithValues("IdCentroCusto", movimentation.CostCenterId);

				cmd.AddParameterWithValues("DataMovimentacao", movimentation.MovimentationDate.Truncate());

				if (movimentation.Observation != null)
					cmd.AddParameterWithValues("Observacoes", movimentation.Observation);

				cmd.AddParameterWithValues("IdContribuinte", movimentation.ContributorId);

				if (movimentation.PaymentMethod != null)
				{
					cmd.AddParameterWithValues("IdTipoPagamento", (int)movimentation.PaymentMethod.Method);

					if (!string.IsNullOrEmpty(movimentation.PaymentMethod.CheckNumber))
						cmd.AddParameterWithValues("NumeroCheque", movimentation.PaymentMethod.Method);
				}

				switch (movimentation.Type)
				{
					case EMovimentationType.Income:
						cmd.CommandText = "AtualizarMovimentacaoEntrada";
						cmd.AddParameterWithValues("IdTipoEntrada", (int)(movimentation as Income).IncomeType);
						break;
					case EMovimentationType.Outcome:
						cmd.CommandText = "AtualizarMovimentacaoSaida";
						break;
					default:
						break;
				}

				cmd.ExecuteNonQuery();
			}
		}
		public void RemoveMovimentation(int id)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "RemoverMovimentacao";
				cmd.AddParameterWithValues("Id", id);

				cmd.ExecuteNonQuery();
			}
		}
		public Movimentation GetMovimentation(int id)
		{
			List<Movimentation> movimentations = GetMovimentations(new MovimentationFilter() { DesiredIds = new int[] { id } }, MovimentationInfo.GeneralData);

			return movimentations != null && movimentations.Count == 1 ? movimentations[0] : null;
		}
		public List<Movimentation> GetMovimentations(MovimentationFilter filter, MovimentationInfo info)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			{
				return _GetMovimentations(conn, filter, info);
			}
		}

		public CostCenter GetCostCenter(int id, CostCenterInfo info)
		{
			List<CostCenter> costCenters = GetCostCenters(new int[] { id }, info);

			return costCenters != null && costCenters.Count == 1 ? costCenters[0] : null;
		}
		public List<CostCenter> GetCostCenters(CostCenterInfo info)
		{
			return GetCostCenters(null, info);
		}
		public List<CostCenter> GetCostCenters(int[] ids, CostCenterInfo info)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			{
				return _GetCostCenters(conn, ids, info);
			}
		}
		public int InsertCostCenter(CostCenter center)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
				using (SqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.CommandText = "InserirCentroCusto";

					cmd.AddParameterWithValues("Nome", center.Name);
					cmd.AddParameterWithValues("Descricao", center.Description);
					cmd.AddParameterWithValues("Codigo", center.Code);

					if (center.ResponsableId.HasValue)
						cmd.AddParameterWithValues("IdResponsavel", center.ResponsableId);

					try
					{
						return cmd.ExecuteWithReturnValue();
					}
					catch (SqlException e)
					{
						if (e.Message.Contains("ALREADYEXISTS"))
							throw new ValidationError("InvalidName", "Já existe um centro de custo com o nome informado.");
						else
							throw e;
					}
				}
		}
		public void UpdateCostCenter(CostCenter center)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "AtualizarCentroCusto";

				cmd.AddParameterWithValues("Id", center.Id);
				cmd.AddParameterWithValues("Nome", center.Name);
				cmd.AddParameterWithValues("Descricao", center.Description);
				cmd.AddParameterWithValues("Codigo", center.Code);

				if (center.ResponsableId.HasValue)
					cmd.AddParameterWithValues("IdResponsavel", center.ResponsableId);

				cmd.ExecuteNonQuery();
			}
		}
		public void RemoveCostCenter(int id)
		{
			using (SqlConnection conn = _ConfigMgr.CreateConnection())
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = "RemoverCentroCusto";
				cmd.AddParameterWithValues("Id", id);

				try
				{
					cmd.ExecuteNonQuery();
				}
				catch (SqlException e)
				{
					if (e.Message.Contains("VINCULOMOVIMENTACAO"))
						throw new InvalidOperationException("Não é possível remover o centro de custo selecionado, pois este possui vínculos com movimentações.");
					else
						throw e;
				}
			}
		}
		#endregion
	}
}
